package com.gz.jdbc;

import org.junit.jupiter.api.Test;
import com.gz.utils.JdbcUtilis;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Batch {//批处理

    @Test
    public void noBatchinsert() throws Exception {
        //传统方式一次添加数据
        Connection connection= JdbcUtilis.getConnection();
        String sql="insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start=System.currentTimeMillis();
        for (int i = 0; i <5000 ; i++) {
            preparedStatement.setString(1,"jack"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.executeUpdate();
        }
        long end=System.currentTimeMillis();
        System.out.println("共耗时: "+(end-start)+"毫秒");// 35154 ms
        JdbcUtilis.close(null,preparedStatement,connection);
    }


    //批处理方式，一次性处理多条sql指令
    @Test
    public void batchInsert() throws Exception {
        //批处理方式添加数据
        Connection connection= JdbcUtilis.getConnection();
        String sql="insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start=System.currentTimeMillis();
        for (int i = 0; i <5000 ; i++) {
            preparedStatement.setString(1,"jack"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.addBatch();
            //当有100条记录时，再批量处理
            //注意！！！一定要把url后面添加 ?rewriteBatchedStatements=true ,否则没有效果
            if((i+1)%1000==0){//满1000条sql
                preparedStatement.executeBatch();//批量执行
                preparedStatement.clearBatch();//清空一下
            }
        }
        long end=System.currentTimeMillis();
        System.out.println("批量耗时: "+(end-start)+"毫秒");// 381 ms
                                                // 与传统方式一个一个添加方式的35154 ms相比可见一斑
        JdbcUtilis.close(null,preparedStatement,connection);
    }

}
